--SELECT * FROM FN_LOAD_RELEASE_CONTACT_PERSONS_DETAILS ( 153, 'ASSEMBLY', -1, 0 )
CREATE FUNCTION FN_LOAD_RELEASE_CONTACT_PERSONS_DETAILS
(
	@RELEASE_ID INT,
	@RELEASE_PS_TYPE VARCHAR(50),
	@VENDOR_ID INT,
	@IS_NEED_TO_CONSIDER_RELEASE_ID INT
)
RETURNS @FN_RESULTS TABLE( ROW_ID INT IDENTITY, VENDOR_ID INT, VENDOR_NAME VARCHAR(50), CONTACT_PERSON_NAME VARCHAR(50), CONTACT_PERSON_EMAIL VARCHAR(50), CONTACT_PERSON_TYPE VARCHAR(50) ) 
AS
BEGIN
	DECLARE @RELEASE_INFO_SETUP_ID 	INT
	DECLARE @VENDOR_NAME 		VARCHAR(50)
	DECLARE @CATEGORY_TYPE 		VARCHAR(50)
	DECLARE @RELEASE_TYPE 		VARCHAR(50)

	IF( @RELEASE_PS_TYPE = 'ASSEMBLY' ) 
	BEGIN
		IF( @IS_NEED_TO_CONSIDER_RELEASE_ID = 0 )
		BEGIN
			SELECT @VENDOR_ID = TO_VENDOR_ID, @RELEASE_TYPE = REQUEST_TYPE FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
		END		
	END
	ELSE IF( @RELEASE_PS_TYPE = 'TEST' ) 
	BEGIN
		IF( @IS_NEED_TO_CONSIDER_RELEASE_ID = 0 )
		BEGIN
			SELECT @VENDOR_ID = TO_VENDOR_ID, @RELEASE_TYPE = RELEASE_TYPE FROM TEST_RELEASE_TABLE WHERE TEST_REL_ID = @RELEASE_ID
		END
	END

	SELECT @RELEASE_INFO_SETUP_ID = RELEASE_INFO_SETUP_ID FROM RELEASE_INFO_SETUP_TABLE WHERE PROCESS_TYPE = @RELEASE_PS_TYPE AND VENDOR_ID = @VENDOR_ID AND CATEGORY = @RELEASE_TYPE

	SELECT @VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = @VENDOR_ID

	INSERT INTO @FN_RESULTS ( VENDOR_ID, VENDOR_NAME, CONTACT_PERSON_NAME, CONTACT_PERSON_EMAIL, CONTACT_PERSON_TYPE )
	SELECT @VENDOR_ID AS VENDOR_ID, @VENDOR_NAME AS VENDOR_NAME, CONTACT_PERSON_NAME AS CONTACT_PERSON_NAME, CONTACT_PERSON_EMAIL AS CONTACT_PERSON_EMAIL, 'INTERNAL' AS CONTACT_PERSON_TYPE
	FROM RELEASE_INFO_CONTACT_PERSON_DETAILS_TABLE WHERE RELEASE_INFO_SETUP_ID = @RELEASE_INFO_SETUP_ID
	UNION
	SELECT VENDOR_ID, VENDOR_NAME, PERSON_NAME AS CONTACT_PERSON_NAME, PERSON_EMAIL AS CONTACT_PERSON_EMAIL, 'VENDOR_CONTACT_PERSONS' AS CONTACT_PERSON_TYPE
	FROM RELEASE_INFO_VENDOR_CONTACT_PERSONS WHERE RELEASE_INFO_SETUP_ID = @RELEASE_INFO_SETUP_ID

	
	RETURN
END
